package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @ClassName VoMapper
 * @description: TODO
 * @date 2025年05月19日
 * @version: 1.0
 */
@Mapper
public interface VoMapper {
    @Select("select s.name name,\n" +
            "       sum(oi.original_amount) amount1,\n" +
            "       count(oi.id) num1,\n" +
            "       count(distinct oi.customer_id) num2,\n" +
            "       sum(oi.actual_amount) amount2,\n" +
            "       sum(p.amount) amount3\n" +
            "from order_info oi\n" +
            "left join shop s on s.id = oi.shop_id\n" +
            "left join payment p on s.id = p.shop_id\n" +
            "where p.status=5\n" +
            "group by s.name;")
    List<Vo1> getList1();

    @Select("select curdate() day,1 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) amount1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) amount2,\n" +
            "       sum(p.reduce_amount) amount3\n" +
            "from order_info oi\n" +
            "left join shop s on s.id = oi.shop_id\n" +
            "left join promotion p on p.id = oi.promotion_id\n" +
            "where timestampdiff(day,oi.create_time,now())<=1\n" +
            "union\n" +
            "select curdate() day,7 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) amount1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) amount2,\n" +
            "       sum(p.reduce_amount) amount3\n" +
            "from order_info oi\n" +
            "         left join shop s on s.id = oi.shop_id\n" +
            "         left join promotion p on p.id = oi.promotion_id\n" +
            "where timestampdiff(day,oi.create_time,now())<=7\n" +
            "union\n" +
            "select curdate() day,30 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) amount1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) amount2,\n" +
            "       sum(p.reduce_amount) amount3\n" +
            "from order_info oi\n" +
            "         left join shop s on s.id = oi.shop_id\n" +
            "         left join promotion p on p.id = oi.promotion_id\n" +
            "where timestampdiff(day,oi.create_time,now())<=30;")
    List<Vo2> getList2();

    @Select("select p.name name,sum(if(oi.rating=5,1,0))/count(oi.id) value from order_info oi\n" +
            "left outer join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on ps.id = od.product_sku_id\n" +
            "left join product_spu p on p.id = ps.product_spu_id\n" +
            "where p.name is not null\n" +
            "group by p.name;")
    List<Vo> getList3();

    @Select("select p.name name,avg(oi.rating) value from order_info oi\n" +
            "left outer join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on ps.id = od.product_sku_id\n" +
            "left join product_spu p on p.id = ps.product_spu_id\n" +
            "where p.name is not null\n" +
            "group by p.name;")
    List<Vo> getList4();

    @Select("select hour(oi.create_time) name,sum(oi.original_amount) value from order_info oi group by hour(oi.create_time);")
    List<Vo> getList5();
}
